//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//*********************************************************

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Workflow.ComponentModel;
using System.Workflow.ComponentModel.Design;
using System.Workflow.ComponentModel.Compiler;
using System.Workflow.ComponentModel.Serialization;
using System.Workflow.Runtime;
using System.Workflow.Activities;
using System.Workflow.Activities.Rules;
using Microsoft.Research.ScientificWorkflow;
using System.Threading;
using System.Data.Common;
using System.Data.SqlClient;

namespace Microsoft.Research.ScientificWorkflow.Activities
{
    /// <summary>
    /// This activity executes the stored procedure on the SQL
    /// server instance.
    /// This acitivity accepts the procedure name and the parameters
    /// along with values.
    /// </summary>
    [Name("Stored Procedure Executor")]
    [Description("Executes a stored procedure on the SQL instance.")]
    [WorkflowCategory("Database")]
    class SqlStoredProcedureExecutor : Activity
    {
        #region Property Definitions

        public static DependencyProperty DatabaseConnectionProperty = DependencyProperty.Register("DatabaseConnection",
            typeof(SqlConnection), typeof(SqlStoredProcedureExecutor));

        /// <summary>
        /// Database Server Connection Object.
        /// </summary>
        [RequiredInputParam]
        [Name("Database Connection")]
        [Description("Connection to the database server.")]
        public SqlConnection DatabaseConnection
        {
            get { return ((SqlConnection)(base.GetValue(SqlStoredProcedureExecutor.DatabaseConnectionProperty))); }
            set { base.SetValue(SqlStoredProcedureExecutor.DatabaseConnectionProperty, value); }
        }

        public static DependencyProperty StoredProcedureNameProperty = DependencyProperty.Register("StoredProcedureName",
            typeof(string), typeof(SqlStoredProcedureExecutor));

        /// <summary>
        /// Stored Procedure Name.
        /// </summary>
        [RequiredInputParam]
        [Name("Stored Procedure Name")]
        [Description("Name of the stored procedure to be executed.")]
        public string StoredProcedureName
        {
            get { return ((string)(base.GetValue(SqlStoredProcedureExecutor.StoredProcedureNameProperty))); }
            set { base.SetValue(SqlStoredProcedureExecutor.StoredProcedureNameProperty, value); }
        }

        public static DependencyProperty ParameterNameValueProperty = DependencyProperty.Register("ParameterNameValue",
            typeof(string), typeof(SqlStoredProcedureExecutor));

        /// <summary>
        /// Parameter names with values specified in the format
        /// param1,value1,param2,value2.....
        /// </summary>
        [InputParam]
        [Name("Parameter Details")]
        [Description("Name of the stored procedure parameters with values.")]
        public string ParameterNameValue
        {
            get { return ((string)(base.GetValue(SqlStoredProcedureExecutor.ParameterNameValueProperty))); }
            set { base.SetValue(SqlStoredProcedureExecutor.ParameterNameValueProperty, value); }
        }

        #endregion

        /// <summary>
        /// Calls the DB method to execute the stored procedure.
        /// </summary>
        /// <param name="executionContext"></param>
        /// <returns></returns>
        protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
        {
            DatabaseHelper.Init();
            SqlDataReader rdr = DatabaseHelper.ExecuteStoredProc(this.DatabaseConnection, this.StoredProcedureName,
                this.ParameterNameValue);
            if (rdr != null)
                rdr.Close();

            return ActivityExecutionStatus.Closed;
        }
    }

    /// <summary>
    /// Helper class for interacting with SQL.
    /// </summary>
    public class DatabaseHelper
    {
        /// <summary>
        /// Map for getting the SQL Type from the string representation.
        /// </summary>
        private static Dictionary<string, System.Data.SqlDbType> paramMap = null;

        /// <summary>
        /// Initializer
        /// </summary>
        public static void Init()
        {
            if (null != paramMap)
                return;

            paramMap = new Dictionary<string, System.Data.SqlDbType>();
            paramMap.Add("INT", System.Data.SqlDbType.Int);
            paramMap.Add("NVARCHAR", System.Data.SqlDbType.NVarChar);
            paramMap.Add("CHAR", System.Data.SqlDbType.Char);
            paramMap.Add("FLOAT", System.Data.SqlDbType.Float);
            paramMap.Add("REAL", System.Data.SqlDbType.Real);
            paramMap.Add("VARCHAR", System.Data.SqlDbType.VarChar);
        }


        /// <summary>
        /// Method for parsing the param name value pair to 
        /// </summary>
        private static void GetParametersDetails(string parameters, out List<string> names,
            out List<string> types, out List<string> values)
        {
            names = new List<string>();
            types = new List<string>();
            values = new List<string>();

            if (string.IsNullOrEmpty(parameters))
                return;

            string[] tokens = parameters.Split(new char[] { ',' });
            if (0 != (tokens.Length % 3))
                throw new ArgumentException("Incorrect param name value string");

            for (int i = 0, j = 0; i < tokens.Length; j++)
            {
                names.Insert(j, tokens[i]);
                types.Insert(j, tokens[i + 1]);
                values.Insert(j, tokens[i + 2]);
                i += 3;
            }
        }

        /// <summary>
        /// Converts the string value to the type as specified in the 
        /// type argument.
        /// </summary>
        /// <param name="type"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        private static object GetParamValue(string type, string value)
        {
            switch (type)
            {
                case "INT":
                    return Convert.ToInt32(value);
                case "NVARCHAR":
                    return value;
                case "CHAR":
                    return Convert.ToChar(value);
                case "FLOAT":
                    return Convert.ToSingle(value);
                case "REAL":
                    return Convert.ToDouble(value);
                case "VARCHAR":
                    return value;
                default:
                    throw new ArgumentException("Unmapped Type");
            }
        }

        /// <summary>
        /// This method executed the stored procedure using the
        /// connection object passed.
        /// parameters passed to this method should be in the format
        /// of name value pair as
        /// param1,type1,value1,param2,type2,value2........
        /// </summary>
        public static SqlDataReader ExecuteStoredProc(SqlConnection conn, string spName, string parameters)
        {
            if (null == conn || (System.Data.ConnectionState.Open != conn.State))
                throw new ArgumentException("Connection not Set");

            // Parse the string for parameters to get the name and values.
            List<string> names; List<string> values; List<string> types;

            GetParametersDetails(parameters, out names, out types, out values);

            using (SqlCommand cmd = new SqlCommand(spName, conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                for (int i = 0; i < names.Count; ++i)
                {
                    SqlParameter param = new SqlParameter(names[i], paramMap[types[i].ToUpper()]);
                    param.Value = GetParamValue(types[i].ToUpper(), values[i]);
                    cmd.Parameters.Add(param);
                }

                SqlDataReader reader;
                try
                {
                    reader = cmd.ExecuteReader();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error Occurred while executing the procedure");
                    Console.WriteLine(ex.ToString());
                    return null;
                }
                return reader;
            }
        }

        /// <summary>
        /// This method executes a data fetch sql query on the database and returns
        /// the reader object.
        /// The parameter details are passed in the format 
        /// param1,type1,value1,param2,type2,value2........
        /// </summary>
        public static SqlDataReader ExecuteReaderQuery(SqlConnection conn, string sqlQuery, string paramDetails)
        {
            if (null == conn || (System.Data.ConnectionState.Open != conn.State))
                throw new ArgumentException("Connection not Set");

            // Parse the string for parameters to get the name and values.
            List<string> names; List<string> values; List<string> types;

            GetParametersDetails(paramDetails, out names, out types, out values);

            using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
            {
                cmd.CommandType = System.Data.CommandType.Text;

                for (int i = 0; i < names.Count; ++i)
                {
                    SqlParameter param = new SqlParameter(names[i], paramMap[types[i].ToUpper()]);
                    param.Value = GetParamValue(types[i].ToUpper(), values[i]);
                    cmd.Parameters.Add(param);
                }

                SqlDataReader reader = null;
                try
                {
                    reader = cmd.ExecuteReader();
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error Occurred while executing the procedure");
                    Console.WriteLine(ex.ToString());
                    return null;
                }
                return reader;
            }
        }
    }
}
